<?php
/**
 * crcl/base
 *
 * Copyright 2013 CRCL - André Sabosch - info@crcl.de
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

namespace crcl\base\database\container;

interface IMysql
{
  public function executeSql($sSql, $aBind = []);
}

class ContainerMysql extends Base implements IMysql
{
  public function connect()
  {
    if (!$this->isConnected())
    {
      $this->oDatabase = \mysqli_init();
      if (!$this->oDatabase)
      {
        // @codeCoverageIgnoreStart
        throw new Exception('Init failed',
          Exception::ERROR_MYSQL_INITFAILED,
          array('error_id' => $this->oDatabase->errno,
                'error' => $this->oDatabase->error));
        // @codeCoverageIgnoreEnd
      }
      $bResult = $this->oDatabase->options(\MYSQLI_INIT_COMMAND, 'SET NAMES utf8;');
      if (!$bResult)
      {
        // @codeCoverageIgnoreStart
        throw new Exception('Options failed',
          Exception::ERROR_MYSQL_OPTIONSFAILED,
          array('option' => 'SET NAMES utf8;',
                'error_id' => $this->oDatabase->errno,
                'error' => $this->oDatabase->error));
        // @codeCoverageIgnoreEnd
      }
      $bResult = $this->oDatabase->options(\MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
      if (!$bResult)
      {
        // @codeCoverageIgnoreStart
        throw new Exception('Options failed',
          Exception::ERROR_MYSQL_OPTIONSFAILED,
          array('option' => 'int and float native;',
                'error_id' => $this->oDatabase->errno,
                'error' => $this->oDatabase->error));
        // @codeCoverageIgnoreEnd
      }
      @$bResult = $this->oDatabase->real_connect(
        $this->oDsn->getHost(),
        $this->oDsn->getUser(),
        $this->oDsn->getPassword(),
        $this->oDsn->getDatabase(),
        $this->oDsn->getPort());
      if (!$bResult)
      {
        throw new Exception('Connect failed',
          Exception::ERROR_MYSQL_CONNECTFAILED,
          array('error_id' => $this->oDatabase->connect_errno,
                'error' => $this->oDatabase->connect_error));
      }
      $bResult = $this->oDatabase->set_charset('utf8');
      if (!$bResult)
      {
        // @codeCoverageIgnoreStart
        throw new Exception('Charset failed',
          Exception::ERROR_MYSQL_CHARSETFAILED,
          array('error_id' => $this->oDatabase->errno,
                'error' => $this->oDatabase->error));
        // @codeCoverageIgnoreEnd
      }
      $this->bConnected = \TRUE;

      $this->oValidatorIdentifier = \crcl\base\Validator::mysqlIdentifier();
      $this->oFilterIdentifier = \crcl\base\Filter::mysqlIdentifier();
    }
  }

  public function disconnect()
  {
    if ($this->isConnected())
    {
      $this->oDatabase->close();
      $this->bConnected = \FALSE;
    }
  }

  public function executeSql($sSql, $aBind = [])
  {
    $this->connect();
    $sSql = $this->secureSql($sSql);
    $aBind = $this->secureBind($aBind);

    foreach ($aBind as $sBind => $sValue)
    {
      $sSql = \str_replace(':' . $sBind, $sValue, $sSql);
    }

    $this->lastQuery = $sSql;

    $bResult = $this->oDatabase->real_query($sSql);
    if (!$bResult)
    {
      throw new Exception($this->oDatabase->error, Exception::ERROR_MYSQL_EXECUTESQL,
        $this->oDatabase->error_list);
    }

    $iFields = $this->oDatabase->field_count;
    if ($iFields !== 0)
    {
      $oResult = $this->oDatabase->store_result();
      $this->aData = $oResult->fetch_all(\MYSQLI_ASSOC);
      $oResult->free_result();
    }

    return $this;
  }

  public function createTable($sTable, $aDefinition)
  {
    $sTable = $this->checkIdentifier($sTable);

    $sQuery = 'CREATE TABLE ' . $sTable . ' (';

    $bFirst = \TRUE;
    foreach ($aDefinition as $sField => $aField)
    {
      if (!$bFirst)
      {
        $sQuery .= ', ';
      }
      if ($bFirst)
      {
        $bFirst = \FALSE;
      }
      $sField = $this->checkIdentifier($sField);
      $sQuery .= $sField . ' ';
      if (\is_string($aField))
      {
        $aField = ['type' => $aField];
      }
      if (isset($aField['type']))
      {
        $sQuery .= $aField['type'];
      }
      else
      {
        throw new Exception('Missing type', Exception::ERROR_MYSQL_CREATETABLE,
          array('definition' => $aDefinition));
      }
    }
    $sQuery .= ');';
    try
    {
      $this->executeSql($sQuery);
    }
    catch (Exception $e)
    {
      throw new Exception($e->getMessage(), Exception::ERROR_MYSQL_CREATETABLE,
        array('table' => $sTable, 'definition' => $aDefinition));
    }

    return \TRUE;
  }

  public function droptable($sTable)
  {
    $sTable = $this->checkIdentifier($sTable);

    $sQuery = 'DROP TABLE ' . $sTable . ';';

    try
    {
      $this->executeSql($sQuery);
    }
    catch (Exception $e)
    {
      throw new Exception($e->getMessage(), Exception::ERROR_MYSQL_DROPTABLE,
        array('table' => $sTable));
    }

    return \TRUE;
  }

  public function quote($value)
  {
    $this->connect();

    $value = $this->oDatabase->real_escape_string($value);
    $value = parent::quote($value);

    return $value;
  }

  public function insert($sTable, $aData)
  {
    $sTable = $this->checkIdentifier($sTable);

    $sQuery = 'INSERT INTO ' . $sTable . ' ';

    $sColumns = '(';
    $sValues = '(';
    $bFirst = \TRUE;
    if (\is_array($aData) && (!empty($aData)))
    {
      foreach ($aData as $col => $value)
      {
        if (!$bFirst)
        {
          $sColumns .= ', ';
          $sValues .= ', ';
        }
        else
        {
          $bFirst = \FALSE;
        }
        $sColumns .= $this->oFilterIdentifier->filter($col);
        $sValues .= $this->quote($value);
      }
      $sColumns .= ')';
      $sValues .= ')';

      $sQuery .= $sColumns . ' VALUES ' . $sValues . ';';
      try
      {
        $this->executeSql($sQuery);
      }
      catch (Exception $e)
      {
        throw new Exception($e->getMessage(), Exception::ERROR_MYSQL_INSERT,
          array('table' => $sTable, 'data' => $aData));
      }
    }
    else
    {
      throw new Exception('Invalid data', Exception::ERROR_MYSQL_INSERT,
        array('data' => $aData));
    }

    return \TRUE;
  }

  public function update($sTable, $aData, $sWhere, $aBind = [])
  {
    $sTable = $this->checkIdentifier($sTable);

    $sQuery = 'UPDATE ' . $sTable . ' SET ';

    $bFirst = \TRUE;
    if (\is_array($aData) && (!empty($aData)))
    {
      foreach ($aData as $col => $value)
      {
        if (!$bFirst)
        {
          $sQuery .= ', ';
        }
        else
        {
          $bFirst = \FALSE;
        }

        $sQuery .= $this->oFilterIdentifier->filter($col);
        $sQuery .= ' = ';
        $sQuery .= $this->quote($value);
      }

      $sQuery .= ' WHERE ' . $sWhere . ';';
      try
      {
        $this->executeSql($sQuery, $aBind);
      }
      catch (Exception $e)
      {
        throw new Exception($e->getMessage(), Exception::ERROR_MYSQL_UPDATE,
          array('table' => $sTable, 'data' => $aData));
      }
    }
    else
    {
      throw new Exception('Invalid data', Exception::ERROR_MYSQL_UPDATE,
        array('data' => $aData));
    }

    return \TRUE;
  }

  public function updateById($sTable, $aData, $id)
  {
    if (!\is_int($id))
    {
      throw new Exception('Invalid id', Exception::ERROR_MYSQL_UPDATE,
        array('id' => $id));
    }
    return $this->update($sTable, $aData,  'id = :id', ['id' => $id]);
  }

  public function delete($sTable, $sWhere, $aBind = [])
  {
    $sTable = $this->checkIdentifier($sTable);

    $sQuery = 'DELETE FROM ' . $sTable;
    $sQuery .= ' WHERE ' . $sWhere . ';';

    try
    {
      $this->executeSql($sQuery, $aBind);
    }
    catch (Exception $e)
    {
      throw new Exception($e->getMessage(), Exception::ERROR_MYSQL_DELETE,
        array('table' => $sTable, 'where' => $sWhere));
    }

    return \TRUE;
  }

  public function deleteById($sTable, $id)
  {
    if (!\is_int($id))
    {
      throw new Exception('Invalid id', Exception::ERROR_MYSQL_DELETE,
        array('id' => $id));
    }
    return $this->delete($sTable, 'id = :id', ['id' => $id]);
  }

  public function select($sTable, $aFields, $sWhere, $aBind = [])
  {
    $sTable = $this->checkIdentifier($sTable);

    $sQuery = 'SELECT ';

    if (\is_string($aFields))
    {
      $aFields = [$aFields];
    }

    foreach ($aFields as $sField)
    {
      if ($sField == '*')
      {
        $sQuery .= $sField;
      }
      else
      {
        $sQuery .= $this->checkIdentifier($sField);
      }
    }
    $sQuery .= ' FROM ' . $sTable;
    $sQuery .= ' WHERE ' . $sWhere . ';';

    try
    {
      $this->executeSql($sQuery, $aBind);
    }
    catch (Exception $e)
    {
      throw new Exception($e->getMessage(), Exception::ERROR_MYSQL_SELECT,
        array('table' => $sTable, 'where' => $sWhere));
    }

    return $this;
  }

  public function selectById($sTable, $aFields, $id)
  {
    if (!\is_int($id))
    {
      throw new Exception('Invalid id', Exception::ERROR_MYSQL_SELECT,
        array('id' => $id));
    }
    return $this->select($sTable, $aFields, 'id = :id', ['id' => $id]);
  }

  /** PRIVATE *************************************************************************************/

  private $oDatabase = \NULL;
  private $oValidatorIdentifier = \NULL;
  private $oFilterIdentifier = \NULL;

  private function secureSql($sSql)
  {
    $oValidator = \crcl\base\Validator::mysqlIdentifier();
    $oFilterIdentifier = \crcl\base\Filter::mysqlIdentifier();

    $aMatches = array();
    $iResult = preg_match_all('/`(.+?)`/i', $sSql, $aMatches);
    if ($iResult > 0)
    {
      foreach ($aMatches[0] as $sIdentifier)
      {
        $sOld = $sIdentifier;
        $sIdentifier = $this->checkIdentifier($sIdentifier);
        if ($sOld != $sIdentifier)
        {
          $sSql = \str_replace($sOld, $sIdentifier, $sSql);
        }
      }
    }

    $aMatches = array();
    $iResult = preg_match_all("/'(.+?)'/i", $sSql, $aMatches);
    if ($iResult > 0)
    {
      foreach ($aMatches[1] as $key => $sValue)
      {
        $sOld = $aMatches[0][$key];
        $sValue = $this->quote($sValue);
        if ($sOld != $sValue)
        {
          $sSql = \str_replace($sOld, $sValue, $sSql);
        }
      }
    }
    return $sSql;
  }

  private function secureBind($aBind)
  {
    foreach ($aBind as $key => $value)
    {
      $aBind[$key] = $this->quote($value);
    }
    return $aBind;
  }

  private function checkIdentifier($sIdentifier)
  {
    if (!$this->oValidatorIdentifier->is($sIdentifier))
    {
      $sIdentifier = $this->oFilterIdentifier->filter($sIdentifier);
    }
    $this->oValidatorIdentifier->test($sIdentifier);
    return $sIdentifier;
  }
}